/**
 * 
 */
package org.ccnetconfig.ccdroid;

import java.text.DateFormat;
import java.util.Date;
import java.util.LinkedList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * @author Ryan
 * 
 */
public class DataProvider extends SQLiteOpenHelper {
	public static final String	TAG												= "DataProvider";

	public static final String	DATABASE_NAME							= "ccdroid";
	public static final int			DATABASE_VERSION					= 1;

	public static final String	TABLE_PROJECTS						= "projects";
	public static final String	TABLE_SETTINGS						= "settings";

	public static final String	FIELD_SHARED_ID						= "_id";

	public static final String	FIELD_SETTINGS_HOST				= "host";
	public static final String	FIELD_SETTINGS_INTERVAL		= "interval";

	public static final String	FIELD_PROJECTS_NAME				= "project";
	public static final String	FIELD_PROJECTS_SETTINGS		= "settingsid";
	public static final String	FIELD_PROJECTS_MODIFIED		= "modified";
	public static final String	FIELD_PROJECTS_ENABLED		= "enabled";
	public static final String	FIELD_PROJECTS_ACTIVITY		= "activity";
	public static final String	FIELD_PROJECTS_LASTSTATUS	= "status";
	public static final String	FIELD_PROJECTS_LASTLABEL	= "label";
	public static final String	FIELD_PROJECTS_LASTBUILD	= "lastbuild";
	public static final String	FIELD_PROJECTS_NEXTBUILD	= "nextbuild";
	public static final String	FIELD_PROJECTS_MESSAGE		= "message";
	public static final String	FIELD_PROJECTS_STAGE			= "stage";
	public static final String	FIELD_PROJECTS_CATEGORY		= "category";

	public DataProvider ( Context context ) {
		super ( context, DATABASE_NAME, null, DATABASE_VERSION );
	}

	/*
	 * 
	 * 
	 * @see
	 * android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite
	 * .SQLiteDatabase)
	 */
	@Override
	public void onCreate ( SQLiteDatabase db ) {

		Log.d ( DataProvider.TAG, "creating tables for database" );

		db.execSQL ( "CREATE TABLE " + TABLE_SETTINGS + " ( " + FIELD_SHARED_ID + " INTEGER PRIMARY KEY, "
				+ FIELD_SETTINGS_HOST + " TEXT, " + FIELD_SETTINGS_INTERVAL + " INTEGER)" );

		db.execSQL ( "CREATE TABLE " + TABLE_PROJECTS + " (" + FIELD_SHARED_ID + " INTEGER PRIMARY KEY, "
				+ FIELD_PROJECTS_NAME + " TEXT, " + FIELD_PROJECTS_SETTINGS + " INTEGER, " + FIELD_PROJECTS_MODIFIED
				+ " TEXT, " + FIELD_PROJECTS_ACTIVITY + " TEXT, " + FIELD_PROJECTS_LASTSTATUS + " TEXT, "
				+ FIELD_PROJECTS_LASTLABEL + " TEXT, " + FIELD_PROJECTS_LASTBUILD + " TEXT, " + FIELD_PROJECTS_NEXTBUILD
				+ " TEXT, " + FIELD_PROJECTS_MESSAGE + " TEXT, " + FIELD_PROJECTS_STAGE + " TEXT, " + FIELD_PROJECTS_CATEGORY
				+ " TEXT, " + FIELD_PROJECTS_ENABLED + " INTEGER)" );

	}

	/*
	 * 
	 * 
	 * @see
	 * android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite
	 * .SQLiteDatabase, int, int)
	 */
	@Override
	public void onUpgrade ( SQLiteDatabase db, int oldVersion, int newVersion ) {
		int currentVersion = oldVersion;

		if ( currentVersion != newVersion ) {
			db.execSQL ( "DROP TABLE IF EXISTS " + TABLE_PROJECTS );
			onCreate ( db );
		}

	}

	public LinkedList<Project> getProjects ( SQLiteDatabase db, int settingsId ) throws Exception {
		if ( db == null ) {
			db = this.getReadableDatabase ();
		}
		Cursor c = db.query ( TABLE_PROJECTS, new String[] { FIELD_SHARED_ID, FIELD_PROJECTS_NAME, FIELD_PROJECTS_SETTINGS,
				FIELD_PROJECTS_ACTIVITY, FIELD_PROJECTS_CATEGORY, FIELD_PROJECTS_ENABLED, FIELD_PROJECTS_LASTBUILD,
				FIELD_PROJECTS_LASTLABEL, FIELD_PROJECTS_LASTSTATUS, FIELD_PROJECTS_MESSAGE, FIELD_PROJECTS_MODIFIED,
				FIELD_PROJECTS_NEXTBUILD, FIELD_PROJECTS_STAGE }, FIELD_PROJECTS_SETTINGS + "=" + settingsId, null, null, null,
				null );

		LinkedList<Project> projects = new LinkedList<Project> ();

		while ( c != null && c.moveToNext () ) {
			Project p = createProject ( db, c );
			if ( p != null ) {
				projects.add ( p );
			}
		}
		db.close ();
		return projects;
	}

	public LinkedList<Project> getProjects ( SQLiteDatabase db, Settings settings ) throws Exception {
		if ( settings == null || settings.getId () <= 0 ) { throw new Exception ( "invalid settings argument" ); }
		return getProjects ( db, getSettings ( db, settings.getId () ) );
	}

	public Project getProject ( SQLiteDatabase db, String name ) {
		if ( db == null ) {
			db = this.getReadableDatabase ();
		}

		Cursor c = db.query ( TABLE_PROJECTS, new String[] { FIELD_SHARED_ID, FIELD_PROJECTS_NAME, FIELD_PROJECTS_SETTINGS,
				FIELD_PROJECTS_ACTIVITY, FIELD_PROJECTS_CATEGORY, FIELD_PROJECTS_ENABLED, FIELD_PROJECTS_LASTBUILD,
				FIELD_PROJECTS_LASTLABEL, FIELD_PROJECTS_LASTSTATUS, FIELD_PROJECTS_MESSAGE, FIELD_PROJECTS_MODIFIED,
				FIELD_PROJECTS_NEXTBUILD, FIELD_PROJECTS_STAGE }, FIELD_PROJECTS_NAME + "='" + name + "'", null, null, null,
				null );

		if ( c != null && c.moveToFirst () ) {
			Project p = createProject ( db, c );
			db.close ();
			return p;
		} else {
			db.close ();
			return null;
		}

	}

	public Settings getSettings ( SQLiteDatabase db, int id ) {
		if ( db == null ) {
			db = this.getReadableDatabase ();
		}

		Cursor c = db.query ( TABLE_SETTINGS,
				new String[] { FIELD_SHARED_ID, FIELD_SETTINGS_HOST, FIELD_SETTINGS_INTERVAL }, null, null, null, null, null );

		if ( c != null && c.moveToFirst () ) {
			Settings s = createSettings ( c );
			db.close ();
			return s;
		} else {
			db.close ();
			return null;
		}
	}

	public LinkedList<Settings> getAllSettings ( SQLiteDatabase db ) {
		if ( db == null ) {
			db = this.getReadableDatabase ();
		}

		Cursor c = db.query ( TABLE_SETTINGS,
				new String[] { FIELD_SHARED_ID, FIELD_SETTINGS_INTERVAL, FIELD_SETTINGS_HOST }, null, null, null, null, null );
		LinkedList<Settings> settings = new LinkedList<Settings> ();
		while ( c != null && c.moveToNext () ) {
			Settings s = createSettings ( c );
			if ( s != null )
				settings.add ( s );
		}
		db.close ();
		return settings;
	}

	public boolean deleteSettings ( SQLiteDatabase db, Settings settings ) {
		if ( db == null ) {
			db = this.getWritableDatabase ();
		}

		Log.d ( DataProvider.TAG, "Deleting all projects where settings id = " + settings.getId () );
		db.delete ( TABLE_PROJECTS, FIELD_PROJECTS_SETTINGS + "=" + settings.getId (), null );

		Log.d ( DataProvider.TAG, "Deleting setings for id = " + settings.getId () );
		boolean result = db.delete ( TABLE_SETTINGS, FIELD_SHARED_ID + "=" + settings.getId (), null ) == 1;

		db.close ();
		return result;
	}

	public boolean deleteProject ( SQLiteDatabase db, Project project ) {
		if ( db == null ) {
			db = this.getWritableDatabase ();
		}

		Log.d ( DataProvider.TAG, "Deleting project for id = " + project.getId () );
		boolean result = db.delete ( TABLE_PROJECTS, FIELD_SHARED_ID + "=" + project.getId (), null ) == 1;

		db.close ();
		return result;
	}

	public long insertSettings ( SQLiteDatabase db, Settings settings ) {
		if ( db == null ) {
			db = this.getWritableDatabase ();
		}
		ContentValues values = new ContentValues ();
		values.put ( FIELD_SETTINGS_HOST, settings.getHost () );
		values.put ( FIELD_SETTINGS_INTERVAL, settings.getInterval () );

		long result = db.insert ( TABLE_SETTINGS, null, values );
		db.close ();
		return result;
	}

	public long insertProject ( SQLiteDatabase db, Project project ) {
		if ( db == null ) {
			db = this.getWritableDatabase ();
		}

		ContentValues values = new ContentValues ();
		DateFormat dateFormatter = getDateformatter ();
		String lastBuildDate = project.getLastBuild () == null ? "" : dateFormatter.format ( project.getLastBuild () );
		String nextBuildDate = project.getNextBuild () == null ? "" : dateFormatter.format ( project.getNextBuild () );
		String modifiedDate = dateFormatter.format ( new Date () );

		values.put ( FIELD_PROJECTS_NAME, project.getName () );
		values.put ( FIELD_PROJECTS_SETTINGS, project.getSettings ().getId () );
		values.put ( FIELD_PROJECTS_ACTIVITY, project.getActivity () );
		values.put ( FIELD_PROJECTS_CATEGORY, project.getCategory () );
		values.put ( FIELD_PROJECTS_ENABLED, project.isEnabled () );
		values.put ( FIELD_PROJECTS_LASTBUILD, lastBuildDate );
		values.put ( FIELD_PROJECTS_LASTLABEL, project.getLastLabel () );
		values.put ( FIELD_PROJECTS_LASTSTATUS, project.getLastStatus () );
		values.put ( FIELD_PROJECTS_MESSAGE, project.getMessage () );
		values.put ( FIELD_PROJECTS_MODIFIED, modifiedDate );
		values.put ( FIELD_PROJECTS_NEXTBUILD, nextBuildDate );
		values.put ( FIELD_PROJECTS_STAGE, project.getStage () );

		long result = db.insert ( TABLE_PROJECTS, null, values );
		db.close ();
		return result;
	}

	public boolean updateProject ( SQLiteDatabase db, Project project ) {
		if ( db == null ) {
			db = this.getWritableDatabase ();
		}

		ContentValues values = new ContentValues ();
		DateFormat dateFormatter = getDateformatter ();
		String lastBuildDate = project.getLastBuild () == null ? "" : dateFormatter.format ( project.getLastBuild () );
		String nextBuildDate = project.getNextBuild () == null ? "" : dateFormatter.format ( project.getNextBuild () );
		String modifiedDate = dateFormatter.format ( new Date () );

		values.put ( FIELD_PROJECTS_NAME, project.getName () );
		values.put ( FIELD_PROJECTS_SETTINGS, project.getSettings ().getId () );
		values.put ( FIELD_PROJECTS_ACTIVITY, project.getActivity () );
		values.put ( FIELD_PROJECTS_CATEGORY, project.getCategory () );
		values.put ( FIELD_PROJECTS_ENABLED, project.isEnabled () );
		values.put ( FIELD_PROJECTS_LASTBUILD, lastBuildDate );
		values.put ( FIELD_PROJECTS_LASTLABEL, project.getLastLabel () );
		values.put ( FIELD_PROJECTS_LASTSTATUS, project.getLastStatus () );
		values.put ( FIELD_PROJECTS_MESSAGE, project.getMessage () );
		values.put ( FIELD_PROJECTS_MODIFIED, modifiedDate );
		values.put ( FIELD_PROJECTS_NEXTBUILD, nextBuildDate );
		values.put ( FIELD_PROJECTS_STAGE, project.getStage () );

		boolean result = db.update ( TABLE_PROJECTS, values, FIELD_SHARED_ID + "=" + project.getId (), null ) == 1;
		db.close ();
		return result;
	}

	/**
	 * Updates a settings object
	 * 
	 * @param db
	 * @param settings
	 * @return
	 */
	public boolean updateSettings ( SQLiteDatabase db, Settings settings ) {
		if ( db == null ) {
			db = this.getWritableDatabase ();
		}

		ContentValues values = new ContentValues ();
		values.put ( FIELD_SETTINGS_HOST, settings.getHost () );
		values.put ( FIELD_SETTINGS_INTERVAL, settings.getInterval () );

		boolean result = db.update ( TABLE_SETTINGS, values, FIELD_SHARED_ID + "=" + settings.getId (), null ) == 1;
		db.close ();
		return result;
	}

	private Project createProject ( SQLiteDatabase db, Cursor c ) {
		if ( c == null ) { return null; }
		int pid = c.getInt ( c.getColumnIndex ( FIELD_SHARED_ID ) );
		String pname = c.getString ( c.getColumnIndex ( FIELD_PROJECTS_NAME ) );
		int sid = c.getInt ( c.getColumnIndex ( FIELD_PROJECTS_SETTINGS ) );

		Settings settings = this.getSettings ( db, sid );
		if ( settings == null ) { return null; }

		Project project = new Project ( pid, pname, settings );

		DateFormat dateFormatter = getDateformatter ();
		Date lastBuild = null;
		Date nextBuild = null;
		Date modified = null;
		try {
			lastBuild = dateFormatter.parse ( c.getString ( c.getColumnIndex ( FIELD_PROJECTS_LASTBUILD ) ) );
		} catch ( Exception ex ) {
		}

		try {
			nextBuild = dateFormatter.parse ( c.getString ( c.getColumnIndex ( FIELD_PROJECTS_NEXTBUILD ) ) );
		} catch ( Exception ex ) {
		}

		try {
			modified = dateFormatter.parse ( c.getString ( c.getColumnIndex ( FIELD_PROJECTS_MODIFIED ) ) );
		} catch ( Exception ex ) {
		}

		project.setActivity ( c.getString ( c.getColumnIndex ( FIELD_PROJECTS_ACTIVITY ) ) );
		project.setCategory ( c.getString ( c.getColumnIndex ( FIELD_PROJECTS_CATEGORY ) ) );
		project.setEnabled ( c.getInt ( c.getColumnIndex ( FIELD_PROJECTS_ENABLED ) ) != 0 );
		project.setLastLabel ( c.getString ( c.getColumnIndex ( FIELD_PROJECTS_LASTLABEL ) ) );
		project.setLastStatus ( c.getString ( c.getColumnIndex ( FIELD_PROJECTS_LASTSTATUS ) ) );
		project.setMessage ( c.getString ( c.getColumnIndex ( FIELD_PROJECTS_MESSAGE ) ) );
		project.setStage ( c.getString ( c.getColumnIndex ( FIELD_PROJECTS_STAGE ) ) );
		project.setLastBuild ( lastBuild );
		project.setNextBuild ( nextBuild );
		project.setModified ( modified );

		return project;
	}

	private Settings createSettings ( Cursor c ) {
		int sid = c.getInt ( c.getColumnIndex ( FIELD_SHARED_ID ) );
		String url = c.getString ( c.getColumnIndex ( FIELD_SETTINGS_HOST ) );
		int interval = c.getInt ( c.getColumnIndex ( FIELD_SETTINGS_INTERVAL ) );
		return new Settings ( url, interval, sid );
	}

	private DateFormat getDateformatter ( ) {
		return DateFormat.getDateTimeInstance ( DateFormat.MEDIUM, DateFormat.DEFAULT );
	}

}
